<?php
/**
 * Query_Builder
 * @filesource
 */
/**
 * Namespace
 */
namespace framework\library\database;
/**
 * Dependencies
 */
use \PDO;
/**
 * Query_Builder Class
 *
 * Plurious Database
 *
 * Simple PDO Query Builder.
 * 
 * Provides query syntax and queries database through
 * parent class methods.
 * 
 * Supports standard CRUD Operations with only the most common query syntax
 * abstracted.  Simple queries using WHERE, ORDER BY, and LIMIT are all supported
 * through class methods.
 * 
 * Complex queries are supported through the rawSelect() and query() methods.
 * These methods require the user to construct their own SQL statements.
 * 
 * @author Randy Casburn <randy@plurious.com>
 * @copyright Copyright Plurious Software, all rights reserved
 * @license MIT
 * @license http://www.opensource.org/licenses/mit-license.html
 * @link http://plurious.com/framework/documentation/classes/Front.html
 * @version 2.1
 * 
 * @uses \PDO
 */
class Query_Builder {

    /** Holds Column name for sorting operations */
    private $order_by_col;

    /** Holds sorting direction (ASC, DESC) for sorting operations */
    private $order_direction;

    /**
     * Creates PDO Parameterized INSERT statement and executes the INSERT
     * 
     * @param array $data
     * @param string $table
     * @return int Id of inserted record
     */
    public function insert($table, Array $data) {
        $table = DB_PREFIX . $table;
        $cols = str_replace(':', '', $this->getColumns($data));
        // Cols and query params could legitimately be empty strings
        if ($cols) {
            $cp = ' (' . $cols . ')  values (' . implode(',', array_keys($data)) . ')';
        } else {
            $cp = ';';
        }
        $sql = 'INSERT INTO seeds () VALUES();';
        //$sql = " INSERT INTO $table$cp";
        $statement = $this->exec($sql, $data);
        $this->testOutput("Insert", $table, $data, $statement->rowCount());
        return $this->lastInsertId();
    }

    /**
     * Creates PDO Parameterized SELECT statement and executes the SELECT
     * 
     * @param string $table
     * @param array $where
     * @param int $limit
     * @return array Associative Array of DB rows
     */
    public function select($table, Array $where, $limit=1000) {
        $table = DB_PREFIX . $table;
        $query = $this->whereEquals("SELECT * FROM " . DB_PREFIX . " $table", $where);
        $statement = $this->exec($query['sql'] . " LIMIT $limit", $query['data']);
        $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        $this->testOutput("Select", $query['sql'], $query['data'], $statement->rowCount());
        return $result;
    }

    /**
     * Creates PDO Parameterized SELECT statement and executes the SELECT
     * 
     * @param string $table
     * @param array $where
     * @param int $limit
     * @return array Associative Array of DB rows
     */
    public function selectCols($table, Array $cols, Array $where, $limit=1000) {
        $table = DB_PREFIX . $table;
        $colsString = implode(',', $cols);
        $query = $this->whereEquals("SELECT $colsString FROM " . DB_PREFIX . " $table", $where);
        $statement = $this->exec($query['sql'] . " LIMIT $limit", $query['data']);
        $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        $this->testOutput("Select", $query['sql'], $query['data'], $statement->rowCount());
        return $result;
    }

    /**
     * Creates PDO Parameterized DELETE statement and executes the DELETE
     * 
     * This method will not execute without a defined where clause.
     * 
     * @param string $table
     * @param array $where
     * @return int Count of affected rows
     */
    public function delete($table, Array $where) {
        $table = DB_PREFIX . $table;
        if (empty($where)) {
            return null;
        }
        $query = $this->whereEquals("DELETE FROM $table", $where);
        $statement = $this->exec($query['sql'], $query['data']);
        $this->testOutput("Delete", $query['sql'], $query['data'], $statement->rowCount());
        return $statement->rowCount();
    }

    /**
     * Creates PDO Parameterized UPDATE statement and executes the UPDATE
     * 
     * @param array $new_values
     * @param string $table
     * @param array $where
     * @return int Count of affected rows
     */
    public function update(Array $new_values, $table, Array $where) {
        $table = DB_PREFIX . $table;
        $sql = "UPDATE $table SET";
        $keys = array_keys($new_values);
        $values = array_values($new_values);
        foreach ($keys as $key) {
            $sql .= " ". $key . " = ?, ";
        }
        $sql = rtrim($sql, ", ");
        $query = $this->whereEquals($sql, $where);
        $values = array_merge($values, $query['data']);
        $statement = $this->exec($query['sql'], $values);
        $this->testOutput("Update", $query['sql'], $new_values, $statement->rowCount());
        return $statement->rowCount();
    }

    /**
     * Combines SQL WHERE clause with sql passed into method.
     * 
     * @param string $sql
     * @param array $where
     * @return array Index 0 contains the sql statement, Index 1 contains the data
     */
    public function whereEquals($sql, Array $where) {
        if (!empty($where)) {
            $sql .= " WHERE ";
        }
        $keys = array_keys($where);
        $values = array_values($where);
        foreach ($keys as $key) {
            $sql .= $key . " = ? AND ";
        }
        $sql = rtrim($sql, "AND ");
        $query = array('sql' => $sql, 'data' => $values);
        return $query;
    }

    /**
     * Sets the Column and Direction for sort operations.
     * 
     * NOTE: you must reset these parameters when finished with them. Otherwise
     * all results following will be sorted using these criteria.
     * 
     * To clear the sort criteria simply call this method with 
     * no parameters passed -- setOrder();
     * 
     * @param string $col The name of the column to sort results on
     * @param string $dir Should be ASC or DESC 
     */
    public function setOrder($col = '', $dir='') {
        $this->order_by_col = $col;
        $this->order_direction = $dir;
    }

    /**
     * Pretty prints a debug dump of the operation performed.
     * 
     * @param string $title Test Title
     * @param string $sql SQL statement
     * @param array $values Data used for query
     * @param int $numRows Affected Row Count
     * @param boolean $isQuery for constructing the SQL statement
     * @return null If not in debug mode 
     */
    public function testOutput($title, $sql, $values, $numRows, $isQuery = false) {
        if (!DATABASE_DEBUG) {
            return;
        }
        if ($title == 'Insert') {
            $sql = DB_PREFIX . $sql;
        }
        echo "=========================================================<br>";
        echo "Operation: $title<br>";
        echo "Table/SQL: " . $sql . "<br>";
        echo "Data:<br><pre>";
        print_r($values);
        echo "</pre>";
        echo "rows affected = $numRows<br>";
        echo "=========================================================<br>";
        if ($isQuery) {
            $stmt = $this->db->prepare($sql);
            $result = $stmt->execute($values);
            print_r($stmt->fetchAll(PDO::FETCH_ASSOC));
        }
    }

}
